package com.net.dao;

import com.net.pojo.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    //验证登录信息的合法性
    public boolean isValid(String userName, String userPwd) throws SQLException {
        boolean result = false;//默认是不合法
        //连接数据库
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.创建连接数据库的连接对象
            Class.forName("com.mysql.cj.jdbc.Driver");
            String user = "root";
            String pwd = "abcdefg";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, user, pwd);
            //2.准备sql语句，需要PreparedStatement类对象
            //String sql = "select * from user where name='" + userName + "' and password='" + userPwd + "'";
            /*select * from user where name='1' or '1'='1' and password='1' or '1'='1'*/
            String sql = "select * from user where name=? and password=?";
            ps = con.prepareStatement(sql);
            ps.setObject(1, userName);
            ps.setObject(2, userPwd);
            //3.执行sql语句
            rs = ps.executeQuery();
            if (rs.next()) {
                result = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        }
        return result;
    }

    //插入用户信息
    public boolean insert(User user) throws SQLException {
        //将传入的用户名和密码入库
        boolean result = false;//默认插入操作实报
        //连接数据库
        Connection con = null;
        PreparedStatement ps = null;
        try {
            //1.创建连接数据库的连接对象
            Class.forName("com.mysql.cj.jdbc.Driver");
            String dbUser = "root";
            String pwd = "abcdefg";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, dbUser, pwd);
            String sql = "insert into user(name,password)values (?,?)";
            ps = con.prepareStatement(sql);
            ps.setObject(1, user.getName());
            ps.setObject(2, user.getPassword());
            ps.execute();
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
        }
        return result;
    }

    //修改用户信息
    //删除用户信息
    //查询用户信息
    public List<User> query(String keyword) throws SQLException {
        //连接数据库
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个用户对象列表，目前没有任何对象
        List<User> userList = new ArrayList<User>();
        try {
            //1.创建连接数据库的连接对象
            Class.forName("com.mysql.cj.jdbc.Driver");
            String user = "root";
            String pwd = "abcdefg";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, user, pwd);
            String sql = "select * from user where name  like ?";
            ps = con.prepareStatement(sql);
            ps.setObject(1, "%" + keyword + "%");
            //3.执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                User tUser=new User(rs.getString("name"),rs.getString("password"));
                userList.add(tUser);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        }
        return userList;
    }
}
//这是新的版本